const mysql = require('mysql'); // 引入mysql模块
const express = require("express"); // 引入express模块
const app = express(); // 实例化express模块 用app来表示
app.use(express.static('public'))

let con = mysql.createConnection({ // 配置mysql数据库
  host: '127.0.0.1', // 主机名
  port: '3306', // 默认端口
  user: 'han', // 连接的名字
  password: '777', // 连接的密码
  database: 'qing-data1' // 连接的数据库
});

con.connect(); // 与数据库建立连接

// 解决跨域问题
app.all('*', function(req, res, next) {
  res.header('Access-Control-Allow-Origin', '*');
  res.header('Access-Control-Allow-Headers', '*');
  res.header('Content-Type', 'application/json;charset=utf-8');
  res.header('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE, OPTIONS');
  next();
});


//用户注册
app.post('/register', function(req, res) {
  var sql = 'insert into user(user_id,user_account,user_password) values(' +
    new Date().getTime() + ',' +
    req.query.user_account + ',' +
    req.query.user_password + ')'
  con.query(sql, function(err, result) { //连接数据库 传入sql语句 回调函数得到结果
    try {
      res.send("成功") //查询结果响应给请求方
    } catch (err) {
      console.log("插入失败");
    }
  });
})

//用户登录
app.get('/login/:user_account', function(req, res) {
  var sql = 'select user_password,user_id from user where user_account=' + req.params.user_account;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



// 查询小贴士
app.get('/home/tips', function(req, res) {
  var sql = 'select tips_id,tips_img from tips';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})


//查询小贴士相关内容
app.get('/home/tips/:id', function(req, res) {
  var sql = 'select tips_con,tips_img from tips where tips_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//查询指南
app.get('/home/guide', function(req, res) {
  var sql = 'select guide_id.guide_name,guide_img from guide';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//查询指南相关内容
app.get('/home/guide/:id', function(req, res) {
  // console.log(req.params.id)
  var sql = 'select guide_name,guide_img,guide_con,guide_ref from guide where guide_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//获取食谱
//改成get请求
app.post('/recipes/menu', function(req, res) {
  var sql = 'select menu_id,menu_name,menu_img,menu_tag,is_collect from menu where menu_ingre1 in(' +
    req.query.one + ',' +
    req.query.tow + "," +
    req.query.three + ") and menu_ingre2 in(" +
    req.query.one + ',' +
    req.query.tow + "," +
    req.query.three + ") and menu_ingre3 in(" +
    req.query.one + ',' +
    req.query.tow + "," +
    req.query.three + ")"
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})


//获取食谱相关内容
app.get('/recipes/menu/:id', function(req, res) {
  var sql = 'select menu_name,menu_video,menu_intro,menu_stuff,menu_step,menu_car,menu_pr,menu_fat from menu where menu_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//点击改变收藏
app.post('/collect/change/:id', function(req, res) {
  var change = req.query.count % 2 == 1 ? 1 : 0
  console.log(change)
  var sql = 'update menu set is_collect=' + change + ' where menu_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//提交记录
app.post('/record/submit', function(req, res) {
  //recode_date
  var month = new Date().getMonth() + 1
  var date = '' + new Date().getFullYear() + month + new Date().getDate()
  //recode_meal
  var hour = new Date().getHours()
  console.log(hour)
  var meal
  if (hour > 5 && hour < 10) {
    meal = "1"
  } else if (hour >= 10 && hour < 14) {
    meal = "2"
  } else if (hour > 17 && hour < 21) {
    meal = "3"
  } else {
    meal = "4"
  }

  console.log(req.query.menu_id)
  console.log(req.query.user_id)

  var sql = 'insert into record values(' +
    new Date().getTime() + ',' +
    date + ',' +
    meal + ',' +
    req.query.menu_id + "," +
    req.query.user_id + ')'
  con.query(sql, function(err, result) { //连接数据库 传入sql语句 回调函数得到结果
    try {
      res.send("成功") //查询结果响应给请求方
    } catch (err) {
      console.log("插入失败");
    }
  });
})



//获取记录
app.get('/record/:id', function(req, res) {
  var sql = 'select record_id,record_date,record_meal,meun_id from record where user_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



// 用户修改信息：/submit/message/:id
app.post('/submit/message/:id', function(req, res) {
  for (let item in req.query) {
    console.log(item, req.query[item])
    var sql = 'update user set ' + item + '=' + req.query[item] + ' where user_id=' + req.params.id
    console.log(sql)
    con.query(sql, function(err, result) {
      try {
        res.send(result)
      } catch (err) {
        console.log("修改失败");
      }
    })
  }
});



// 获取用户信息：/obtain/message/:id
app.get('/obtain/message/:id', function(req, res) {
  var sql = 'select * from user where user_id=' + req.params.id;
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})



//后台接口
// 1.查询用户信息：/user/select
app.get('/user/select', function(req, res) {
  var sql = 'select * from user';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})

// 5.查询小贴士：/tips/get
app.get('/tips/get', function(req, res) {
  var sql = 'select * from tips';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})
// 2.添加用户信息：/tips/add
app.post('/tips/add', function(req, res) {
  var key = [];
  var value = [];
  for (let item in req.query) {
    key.push(item)
    value.push(req.query[item])
  }
  var sql = 'insert into tips('
  for (i = 0; i < key.length - 1; i++) {
    sql = sql + key[i] + ',';
  }
  sql = sql + key[key.length - 1] + ') values('
  for (j = 0; j < value.length - 1; j++) {
    sql = sql + value[j] + ',';
  }
  sql = sql + value[value.length - 1] + ')'
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("插入失败");
    }
  });

})
// 3.修改用户信息：/tips/alter
app.post('/tips/alter/:id', function(req, res) {
  console.log(req.params.id)
  for (let item in req.query) {
    var sql = 'update tips set ' + item + '=' + req.query[item] + 'where tips_id=' + req.params.id
    console.log(sql)
    con.query(sql, function(err, result) {
      try {
        res.send(result)
      } catch (err) {
        console.log("修改失败");
      }
    });
  }
})
// 4.删除小贴士信息：/tips/delet
app.post('/tips/delete', function(req, res) {
  console.log(req.query.tips_id);
  var sql = 'delete from tips where tips_id=' + req.query.tips_id
  con.query(sql, function(err, result) {
    try {
      res.send(result);
    } catch (err) {
      console.log("删除失败");
    }
  });

})
// 6.查询指南:/guide/get
app.get('/guide/get', function(req, res) {
  var sql = 'select * from guide';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})
// 7.查询记录:/record/get
app.get('/record/get', function(req, res) {
  var sql = 'select * from record';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})
// 8.查询食谱分类:/assort/get
// app.get('/assort/get', function(req, res) {
//   var sql = 'select * from assort';
//   con.query(sql, function(err, result) {
//     try {
//       res.send(result)
//     } catch (err) {
//       console.log("查询失败");
//     }
//   });
// })
// 9.查询食谱:/menu/get
app.get('/menu/get', function(req, res) {
  var sql = 'select * from menu';
  con.query(sql, function(err, result) {
    try {
      res.send(result)
    } catch (err) {
      console.log("查询失败");
    }
  });
})


app.listen(8080, () => { // 监听8033端口
  console.log("8080工作中...");
});